/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package co.edo.unal.telemedicina.data;

import java.io.FileInputStream;
import java.io.IOException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Hashtable;
import java.util.List;
import java.util.Properties;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author lariverosc
 */
public class ExtractDataDAO {

    /**
     *
     */
    protected Connection connection;
    /**
     *
     */
    protected Statement statement;
    /**
     *
     */
    private boolean anonymize = true;
    /**
     *
     */
    private PreparedStatement isMainAct;
    /**
     *
     */
    private PreparedStatement isAnswerAct;
    /**
     *
     */
    private PreparedStatement listSpecialtysIDs;
    /**
     *
     */
    private PreparedStatement getCaseIDsbySpecialty;
    /**
     *
     */
    private PreparedStatement getSpecialtyName;
    /**
     *
     */
    private PreparedStatement getSpecialtyForCase;
    /**
     *
     */
    private PreparedStatement getAssociatedAct;
    /**
     *
     */
    private PreparedStatement getImageActID;
    /**
     *
     */
    private PreparedStatement getEntityInfo;
    /**
     *
     */
    private PreparedStatement getCaseInfo;
    /**
     *
     */
    private PreparedStatement getImageInfo;
    /**
     *
     */
    private PreparedStatement getObservationInfo;
    /**
     *
     */
    private PreparedStatement getAnswerInfo;
    /**
     *
     */
    private PreparedStatement listRelatedActIDs;
    /**
     *
     */
    private PreparedStatement listRelatedMonitoring;
    /**
     *
     */
    private PreparedStatement listRelatedAnswers;
    /**
     *
     */
    private PreparedStatement listRelatedCIE;
    /**
     *
     */
    private PreparedStatement listRelatedADCIE;
    /**
     *
     */
    private PreparedStatement listRelatedENTRY;
    /**
     *
     */
    private PreparedStatement relatedPatientRole;
    /**
     *
     */
    private PreparedStatement relatedDoctorRole;
    /**
     *
     */
    private PreparedStatement relatedAgentRole;
    /**
     *
     */
    private PreparedStatement relatedSpecialistRole;

    public ExtractDataDAO() {
    }

    public void connect() {
        try {
            //InitConnection
            Class.forName("org.postgresql.Driver");
            String serverIP, DBName, DBuser, DBpass;
            serverIP = "localhost";
            DBName = "saruro";
            DBuser = "hl7owner";
            DBpass = "123456";
            connection = DriverManager.getConnection("jdbc:postgresql://" + serverIP + ":5432/" + DBName, DBuser, DBpass);
            statement = connection.createStatement();
        } catch (SQLException se) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, se);
        } catch (ClassNotFoundException cnfe) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, cnfe);
        }
    }

    public void init() throws SQLException {
        listSpecialtysIDs = connection.prepareStatement("SELECT mnemonic FROM specialtytype");
        isMainAct = connection.prepareStatement("SELECT count(*) AS c FROM act WHERE classcode='ENC' AND act_id=? ");
        isAnswerAct = connection.prepareStatement("SELECT count(*) AS c FROM act WHERE classcode='CATEGORY' AND act_id=? ");
        getCaseIDsbySpecialty = connection.prepareStatement("SELECT a.act_id FROM act a, patientencounter p WHERE a.classcode ='ENC' AND a.act_id=p.act_id AND p.spcode=?");
        getSpecialtyForCase = connection.prepareStatement("SELECT p.spcode FROM act a, patientencounter p WHERE a.classcode ='ENC' AND a.act_id=p.act_id AND a.act_id=?");
        getSpecialtyName = connection.prepareStatement("SELECT st.printname_es from specialtytype st WHERE st.mnemonic=? ");
        getImageActID = connection.prepareStatement("SELECT act_id from act act WHERE act.title ilike ?");
        getAssociatedAct = connection.prepareStatement("SELECT act.act_id FROM act act,actrelationship rel WHERE act.act_id=rel.acttarget_id AND rel.actsource_id=? AND act.classcode='ENC' AND rel.typecode='COMP'");
        getEntityInfo = connection.prepareStatement("SELECT  per.ent_id AS ent_id, per.documentType AS documentType,ent.beginexistencetime AS beginexistencetime, per.document AS document, ent.name AS name, sbj.administrativeGenderCode AS administrativeGenderCode,"
                + "ent.beginexistencetime AS beginexistencetime, ent.telecom AS telecom,per.addr AS addr, ent.description AS description,sbj.bloodType AS bloodType,per.maritalStatusCode AS maritalStatusCode,per.educationLevelCode AS educationLevelCode,"
                + "per.livingArrangementCode AS livingArrangementCode, rol.entscoper_id AS scoperid FROM entity ent,person per,livingSubject sbj,role rol WHERE rol.rol_id=? AND rol.entplayer_id=ent.ent_id AND ent.ent_id=per.ent_id AND per.ent_id=sbj.ent_id;");
        getCaseInfo = connection.prepareStatement("SELECT act.title AS title, act.text AS text, act.effectivetime AS effectivetime, act.activitytime AS activitytime, act.priorityCode AS priorityCode, act.independentInd AS independentInd, act.reasonCode AS reasonCode, "
                + "act.languageCode AS languageCode, cup.nombre AS cups_id, enc.feedback AS feedback, enc.reason AS reason, enc.spcode AS spcode FROM act act, patientencounter enc,cups cup WHERE act.act_id=enc.act_id AND enc.cups_id=cup.id AND act.act_id=?;");
        listRelatedActIDs = connection.prepareStatement("SELECT act_src.act_id,act_src.classcode,act_src.statuscode FROM act act_tar,act act_src, actrelationship rel WHERE (rel.typecode='COMP' OR rel.typecode='ARR') AND rel.acttarget_id=act_tar.act_id AND rel.actsource_id=act_src.act_id AND act_tar.act_id = ? ");
        listRelatedMonitoring = connection.prepareStatement("SELECT act_rel.act_id,act_rel.classcode,act_rel.statuscode from act act_mon,act act_rel,actrelationship rel  WHERE rel.acttarget_id=act_mon.act_id AND rel.actsource_id=act_rel.act_id AND act_mon.classcode='MPROT' AND act_mon.act_id=? ");
        listRelatedAnswers = connection.prepareStatement("SELECT * FROM act act_cat,act act_enc, actrelationship actrel WHERE actrel.actsource_id=act_enc.act_id AND actrel.acttarget_id=act_cat.act_id AND act_cat.classcode='CATEGORY' AND act_cat.statuscode='completed' AND act_enc.classcode='ENC' AND act_enc.act_id=? ");
        listRelatedCIE = connection.prepareStatement("SELECT cie.printname_es AS printname_es FROM act act_cat,category cat, cie cie WHERE act_cat.act_id=cat.act_id AND cat.cie_id=cie.mnemonic AND act_cat.act_id=? ");
        listRelatedADCIE = connection.prepareStatement("SELECT cie.printname_es AS printname_es FROM  act act_cat,act act_cie,cie cie, actrelationship rel WHERE rel.acttarget_id=act_cat.act_id AND rel.actsource_id=act_cie.act_id AND cie.mnemonic=act_cie.title AND act_cat.act_id=? ");
        listRelatedENTRY = connection.prepareStatement("SELECT act_ent.text AS diag FROM act act_ent,act act_cat,actrelationship rel WHERE  rel.acttarget_id=act_cat.act_id AND rel.actsource_id=act_ent.act_id AND act_ent.classcode='ENTRY' AND act_cat.act_id=? ");
        getImageInfo = connection.prepareStatement("SELECT act.title AS title, act.text AS text, act.effectiveTime AS effectiveTime, act.activityTime AS activityTime, act.reasonCode AS reasonCode, act.languageCode AS languageCode, "
                + " img.filesize AS filesize FROM act act, diagnosticImage img WHERE act.act_id=img.act_id AND act.classcode='DGIMG' AND act.act_id=?;");
        getObservationInfo = connection.prepareStatement("SELECT act.title AS title, act.text AS text, act.effectiveTime AS effectiveTime, act.activityTime AS activityTime, act.availabilityTime AS availabilityTime, act.reasonCode AS reasonCode, act.languageCode AS languageCode, "
                + "obs.value AS value, obs.interpretationCode AS interpretationCode, obs.methodCode AS methodCode, obs.targetSiteCode AS targetSiteCode FROM act act, observation obs WHERE act.act_id=obs.act_id AND act.classcode='OBS' AND act.act_id=?;");
        getAnswerInfo = connection.prepareStatement("SELECT act.title AS title, act.text AS text, act.effectiveTime AS effectiveTime, act.activityTime AS activityTime, act.availabilityTime AS availabilityTime, act.reasonCode AS reasonCode, act.languageCode AS languageCode "
                + " FROM act act WHERE act.classcode='CATEGORY' AND act.act_id=?;");
        relatedPatientRole = connection.prepareStatement("SELECT * FROM get_related_patient_role(?);");
        relatedDoctorRole = connection.prepareStatement("SELECT * FROM get_related_doctor_role(?);");
        relatedAgentRole = connection.prepareStatement("SELECT * FROM get_related_agent_role(?);");
        relatedSpecialistRole = connection.prepareStatement("SELECT * FROM get_related_specialist_role(?);");
    }

    public boolean isAnonymize() {
        return anonymize;
    }

    public void setAnonymize(boolean anonymize) {
        this.anonymize = anonymize;
    }

    /**
     * @param:
     * @return:
     */
    public List<String> listSpecialtyIDS() {
        try {
            List<String> spcodes = new ArrayList<String>();
            ResultSet resultSet = listSpecialtysIDs.executeQuery();
            while (resultSet.next()) {
                spcodes.add(resultSet.getString(1));
            }
            return spcodes;
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    public boolean isMainAct(String actID) {
        try {
            int c = 0;
            isMainAct.setString(1, actID);
            ResultSet resultSet = isMainAct.executeQuery();
            if (resultSet.next()) {
                c = Integer.valueOf(resultSet.getString("c"));
            }
            if (c > 0) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    public boolean isAnswerAct(String actID) {
        try {
            int c = 0;
            isAnswerAct.setString(1, actID);
            ResultSet resultSet = isAnswerAct.executeQuery();
            if (resultSet.next()) {
                c = Integer.valueOf(resultSet.getString("c"));
            }
            if (c > 0) {
                return true;
            }
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return false;
    }

    /**
     * @param:
     * @return:
     */
    public List<String> getCaseIDsBySpecialty(String specialtyID) {
        try {
            List<String> categories = new ArrayList<String>();
            getCaseIDsbySpecialty.setString(1, specialtyID);
            ResultSet resultSet = getCaseIDsbySpecialty.executeQuery();
            while (resultSet.next()) {
                categories.add(resultSet.getString(1));
            }
            return categories;
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     * @param:
     * @return:
     */
    public String getSpecialtyForCase(String caseID) {
        try {
            getSpecialtyForCase.setString(1, caseID);
            ResultSet resultSet = getSpecialtyForCase.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     * @param:
     * @return:
     */
    public String getSpecialtyName(String spcode) {
        try {
            getSpecialtyName.setString(1, spcode);
            ResultSet resultSet = getSpecialtyName.executeQuery();
            if (resultSet.next()) {
                return resultSet.getString(1);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     * @param:
     * @return:
     */
    public String getImageActID(String imageName) {
        try {
            String actID = null;
            getImageActID.setString(1, "%" + imageName + "%");
            ResultSet resultSet = getImageActID.executeQuery();
            if (resultSet.next()) {
                actID = resultSet.getString(1);
            }
            return actID;
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     * @param:
     * @return:
     */
    public String getAssociatedAct(String imageActID) {
        try {
            String actID = null;
            getAssociatedAct.setString(1, imageActID);
            ResultSet resultSet = getAssociatedAct.executeQuery();
            if (resultSet.next()) {
                actID = resultSet.getString(1);
            }
            return actID;
        } catch (SQLException ex) {
            Logger.getLogger(ExtractDataDAO.class.getName()).log(Level.SEVERE, null, ex);
        }
        return null;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public Hashtable<String, String> listPersonalInformation(String rolId) throws SQLException {
        Hashtable<String, String> tempList = new Hashtable<String, String>();
        getEntityInfo.setString(1, rolId);
        ResultSet resultSet = getEntityInfo.executeQuery();
        if (resultSet.next()) {
            if (!anonymize) {
                if ((resultSet.getString("ent_id") == null)) {
                    tempList.put("entid", "");
                } else {
                    tempList.put("entid", resultSet.getString("ent_id"));
                }

                if (resultSet.getString("documentType") == null) {
                    tempList.put("documenttype", "");
                } else {
                    tempList.put("documenttype", resultSet.getString("documentType"));
                }

                if (resultSet.getString("document") == null) {
                    tempList.put("document", "");
                } else {
                    tempList.put("document", resultSet.getString("document"));
                }

                if (resultSet.getString("name") == null) {
                    tempList.put("name", "");
                } else {
                    tempList.put("name", resultSet.getString("name"));
                }
                if (resultSet.getString("telecom") == null) {
                    tempList.put("telecom", "");
                } else {
                    tempList.put("telecom", resultSet.getString("telecom"));
                }

                if (resultSet.getString("addr") == null) {
                    tempList.put("addr", "");
                } else {
                    tempList.put("addr", resultSet.getString("addr"));
                }
                if (resultSet.getString("description") == null) {
                    tempList.put("description", "");
                } else {
                    tempList.put("description", resultSet.getString("description"));
                }
            }
            if (resultSet.getDate("beginexistencetime") == null) {
                tempList.put("Fecha_de_Nacimiento", "");
            } else {
                tempList.put("Fecha_de_Nacimiento", resultSet.getString("beginexistencetime"));
            }

            if (resultSet.getString("administrativeGenderCode") == null) {
                tempList.put("Genero", "");
            } else {
                tempList.put("Genero", resultSet.getString("administrativeGenderCode"));
            }

            if (resultSet.getString("bloodType") == null) {
                tempList.put("Tipo_de_Sangre", "");
            } else {
                tempList.put("Tipo_de_Sangre", resultSet.getString("bloodType"));
            }

            if (resultSet.getString("maritalStatusCode") == null) {
                tempList.put("Estado Civil", "");
            } else {
                tempList.put("Estado Civil", resultSet.getString("maritalStatusCode"));
            }

            if (resultSet.getString("educationLevelCode") == null) {
                tempList.put("Nivel_educativo", "");
            } else {
                tempList.put("Nivel_educativo", resultSet.getString("educationLevelCode"));
            }

//            if (resultSet.getString("livingArrangementCode") == null) {
//                tempList.put("livingarrangementcode", "");
//            } else {
//                tempList.put("livingarrangementcode", resultSet.getString("livingArrangementCode"));
//            }
//
//            if (resultSet.getString("scoperid") == null) {
//                tempList.put("scoperid", "");
//            } else {
//                tempList.put("scoperid", resultSet.getString("scoperid"));
//            }
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public Hashtable<String, String> listCaseInformation(String mainActId) throws SQLException {
        Hashtable<String, String> tempList = new Hashtable<String, String>();
        getCaseInfo.setString(1, mainActId);
        ResultSet resultSet = getCaseInfo.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            if (resultSet.getString("title") == null) {
                tempList.put("Titulo", "");
            } else {
                tempList.put("Titulo", resultSet.getString("title").replace("\n".charAt(0), ' '));
            }

            if (resultSet.getString("text") == null) {
                tempList.put("Texto", "");
            } else {
                tempList.put("Texto", resultSet.getString("text").replace("\n".charAt(0), ' '));
            }

//            if (resultSet.getString("effectivetime") == null) {
//                tempList.put("effectivetime", "");
//            } else {
//                tempList.put("effectivetime", resultSet.getString("effectivetime"));
//            }
//
//            if (resultSet.getString("activitytime") == null) {
//                tempList.put("activitytime", "");
//            } else {
//                tempList.put("activitytime", resultSet.getString("activitytime"));
//            }
//
//            if (resultSet.getString("priorityCode") == null) {
//                tempList.put("prioritycode", "");
//            } else {
//                tempList.put("prioritycode", resultSet.getString("priorityCode"));
//            }
//
//            if (resultSet.getString("independentInd") == null) {
//                tempList.put("independentInd", "");
//            } else {
//                tempList.put("independentInd", resultSet.getString("independentInd"));
//            }
//
//            if (resultSet.getString("reasonCode") == null) {
//                tempList.put("reasoncode", "");
//            } else {
//                tempList.put("reasoncode", resultSet.getString("reasonCode"));
//            }
//
//            if (resultSet.getString("languageCode") == null) {
//                tempList.put("languagecode", "");
//            } else {
//                tempList.put("languagecode", resultSet.getString("languageCode"));
//            }

            if (resultSet.getString("cups_id") == null) {
                tempList.put("cups", "");
            } else {
                tempList.put("cups", resultSet.getString("cups_id"));
            }

//            if (resultSet.getString("feedback") == null) {
//                tempList.put("feedback", "");
//            } else {
//                tempList.put("feedback", resultSet.getString("feedback"));
//            }
//
//            if (resultSet.getString("reason") == null) {
//                tempList.put("reason", "");
//            } else {
//                tempList.put("reason", resultSet.getString("reason"));
//            }
//
//            if (resultSet.getString("spcode") == null) {
//                tempList.put("spcode", "");
//            } else {
//                tempList.put("spcode", resultSet.getString("spcode"));
//            }

        }
        return tempList;
    }

    /**
     *@param
     *@return
     *@throws SQLException
     */
    public Hashtable<String, String> listDiagnosticImageInformation(String actId) throws SQLException {
        Hashtable<String, String> tempList = new Hashtable<String, String>();
        getImageInfo.setString(1, actId);
        ResultSet resultSet = getImageInfo.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            if (resultSet.getString("title") == null) {
                tempList.put("titulo", "");
            } else {
                tempList.put("titulo", resultSet.getString("title").replace("\n".charAt(0), ' '));
            }

            if (resultSet.getString("text") == null) {
                tempList.put("texto", "");
            } else {
                tempList.put("texto", resultSet.getString("text").replace("\n".charAt(0), ' '));
            }

//            if (resultSet.getString("effectiveTime") == null) {
//                tempList.put("effectivetime", "");
//            } else {
//                tempList.put("effectivetime", resultSet.getString("effectiveTime"));
//            }
//
//            if (resultSet.getString("activityTime") == null) {
//                tempList.put("activitytime", "");
//            } else {
//                tempList.put("activitytime", resultSet.getString("activityTime"));
//            }
//
//            if (resultSet.getString("reasonCode") == null) {
//                tempList.put("reasoncode", "");
//            } else {
//                tempList.put("reasoncode", resultSet.getString("reasonCode"));
//            }
//
//            if (resultSet.getString("languageCode") == null) {
//                tempList.put("languagecode", "");
//            } else {
//                tempList.put("languagecode", resultSet.getString("languageCode"));
//            }

            if (resultSet.getString("filesize") == null) {
                tempList.put("tamaño_del_archivo", "");
            } else {
                tempList.put("tamaño_del_archivo", resultSet.getString("filesize"));
            }

        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public Hashtable<String, String> listObservationInformation(String actId) throws SQLException {
        Hashtable<String, String> tempList = new Hashtable<String, String>();
        getObservationInfo.setString(1, actId);
        ResultSet resultSet = getObservationInfo.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            if (resultSet.getString("title") == null) {
                tempList.put("titulo", "");
            } else {
                tempList.put("titulo", resultSet.getString("title").replace("\n".charAt(0), ' '));
            }

            if (resultSet.getString("text") == null) {
                tempList.put("texto", "");
            } else {
                tempList.put("texto", resultSet.getString("text").replace("\n".charAt(0), ' '));
            }

//            if (resultSet.getString("effectiveTime") == null) {
//                tempList.put("effectiveTime", "");
//            } else {
//                tempList.put("effectiveTime", resultSet.getString("effectiveTime"));
//            }
//
//            if (resultSet.getString("activityTime") == null) {
//                tempList.put("activityTime", "");
//            } else {
//                tempList.put("activityTime", resultSet.getString("activityTime"));
//            }
//
//            if (resultSet.getString("availabilityTime") == null) {
//                tempList.put("availabilityTime", "");
//            } else {
//                tempList.put("availabilityTime", resultSet.getString("availabilityTime"));
//            }

//            if (resultSet.getString("reasonCode") == null) {
//                tempList.put("reasonCode", "");
//            } else {
//                tempList.put("reasonCode", resultSet.getString("reasonCode"));
//            }
//
//            if (resultSet.getString("languageCode") == null) {
//                tempList.put("languageCode", "");
//            } else {
//                tempList.put("languageCode", resultSet.getString("languageCode"));
//            }

            if (resultSet.getString("value") == null) {
                tempList.put("valor", "");
            } else {
                tempList.put("valor", resultSet.getString("value"));
            }

//            if (resultSet.getString("interpretationCode") == null) {
//                tempList.put("interpretationCode", "");
//            } else {
//                tempList.put("interpretationCode", resultSet.getString("interpretationCode"));
//            }
//
//            if (resultSet.getString("methodCode") == null) {
//                tempList.put("methodCode", "");
//            } else {
//                tempList.put("methodCode", resultSet.getString("methodCode"));
//            }
//
//            if (resultSet.getString("targetSiteCode") == null) {
//                tempList.put("targetSiteCode", "");
//            } else {
//                tempList.put("targetSiteCode", resultSet.getString("targetSiteCode"));
//            }

        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public Hashtable<String, String> listAnswerInformation(String actId) throws SQLException {
        Hashtable<String, String> tempList = new Hashtable<String, String>();
        getAnswerInfo.setString(1, actId);
        ResultSet resultSet = getAnswerInfo.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            if (resultSet.getString("title") == null) {
                tempList.put("titulo", "");
            } else {
                tempList.put("titulo", resultSet.getString("title").replace("\n".charAt(0), ' '));
            }

            if (resultSet.getString("text") == null) {
                tempList.put("texto", "");
            } else {
                tempList.put("texto", resultSet.getString("text").replace("\n".charAt(0), ' '));
            }

//            if (resultSet.getString("effectiveTime") == null) {
//                tempList.put("effectiveTime", "");
//            } else {
//                tempList.put("effectiveTime", resultSet.getString("effectiveTime"));
//            }
//
//            if (resultSet.getString("activityTime") == null) {
//                tempList.put("activityTime", "");
//            } else {
//                tempList.put("activityTime", resultSet.getString("activityTime"));
//            }
//
//            if (resultSet.getString("availabilityTime") == null) {
//                tempList.put("availabilityTime", "");
//            } else {
//                tempList.put("availabilityTime", resultSet.getString("availabilityTime"));
//            }
//
//            if (resultSet.getString("reasonCode") == null) {
//                tempList.put("reasonCode", "");
//            } else {
//                tempList.put("reasonCode", resultSet.getString("reasonCode"));
//            }
//
//            if (resultSet.getString("languageCode") == null) {
//                tempList.put("languageCode", "");
//            } else {
//                tempList.put("languageCode", resultSet.getString("languageCode"));
//            }

        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public String getRelatedDoctorRole(String mainActId) throws SQLException {
        relatedDoctorRole.setString(1, mainActId);
        ResultSet resultSet = relatedDoctorRole.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            return resultSet.getString(1);
        }
        return null;
    }    //

    /**
     *@param
     *@return
     *@throws SQLException
     */
    public String getRelatedPatientRole(String mainActId) throws SQLException {
        relatedPatientRole.setString(1, mainActId);
        ResultSet resultSet = relatedPatientRole.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            return resultSet.getString(1);
        }
        return null;
    }    //

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public String getRelatedAgentRole(String mainActId) throws SQLException {
        relatedAgentRole.setString(1, mainActId);
        ResultSet resultSet = relatedAgentRole.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            return resultSet.getString(1);
        }
        return null;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public String getRelatedSpecialistRole(String mainActId) throws SQLException {
        relatedSpecialistRole.setString(1, mainActId);
        ResultSet resultSet = relatedSpecialistRole.executeQuery();
        if (resultSet.isBeforeFirst()) {
            resultSet.next();
            return resultSet.getString(1);
        }
        return null;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listRelatedDiagnosticImages(String actId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedActIDs.setString(1, actId);
        ResultSet resultSet = listRelatedActIDs.executeQuery();
        while (resultSet.next()) {
            if (resultSet.getString("classcode").equals("DGIMG") && resultSet.getString("statuscode").equals("normal")) {
                tempList.add(resultSet.getString("act_id"));
            }
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listRelatedObservations(String actId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedActIDs.setString(1, actId);
        ResultSet resultSet = listRelatedActIDs.executeQuery();
        while (resultSet.next()) {
            if (resultSet.getString("classcode").equals("OBS") && resultSet.getString("statuscode").equals("normal")) {
                tempList.add(resultSet.getString("act_id"));
            }
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listMonitoring(String actId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedActIDs.setString(1, actId);
        ResultSet resultSet = listRelatedActIDs.executeQuery();
        while (resultSet.next()) {
            if (resultSet.getString("classcode").equals("MPROT") && resultSet.getString("statuscode").equals("normal")) {
                tempList.add(resultSet.getString("act_id"));
            }
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listRelatedMonitoring(String actId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedMonitoring.setString(1, actId);
        ResultSet resultSet = listRelatedMonitoring.executeQuery();
        while (resultSet.next()) {
            if ((resultSet.getString("classcode").equals("OBS") || resultSet.getString("classcode").equals("SBADM") || resultSet.getString("classcode").equals("DOCCLIN")) && resultSet.getString("statuscode").equals("normal")) {
                tempList.add(resultSet.getString("act_id"));
            }
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public boolean hasRelatedActs(String actId) throws SQLException {
        listRelatedActIDs.setString(1, actId);
        ResultSet resultSet = listRelatedActIDs.executeQuery();
        if (resultSet.next()) {
            return true;
        }
        return false;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listRelatedAnswers(String actId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedAnswers.setString(1, actId);
        ResultSet resultSet = listRelatedAnswers.executeQuery();
        while (resultSet.next()) {
            if (resultSet.getString("statuscode").equals("completed")) {
                tempList.add(resultSet.getString("act_id"));
            }
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listRelatedCIE(String answerActId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedCIE.setString(1, answerActId);
        ResultSet resultSet = listRelatedCIE.executeQuery();
        while (resultSet.next()) {
            tempList.add(resultSet.getString("printname_es"));
        }
        listRelatedADCIE.setString(1, answerActId);
        resultSet = listRelatedADCIE.executeQuery();
        while (resultSet.next()) {
            tempList.add(resultSet.getString("printname_es"));
        }
        return tempList;
    }

    /**
     *
     *@param
     *@return
     *@throws SQLException
     */
    public List<String> listRelatedEntry(String answerActId) throws SQLException {
        List<String> tempList = new ArrayList<String>();
        listRelatedENTRY.setString(1, answerActId);
        ResultSet resultSet = listRelatedENTRY.executeQuery();
        while (resultSet.next()) {
            tempList.add(resultSet.getString("diag"));
        }
        return tempList;
    }

    /**
     * @param:
     * @return:
     */
    public void disconnect() {
        try {
            connection.close();
            statement.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }
}
